/**
 * @swagger
 * /api/high-goods/efficiency-analysis:
 *   get:
 *     tags:
 *       - 高价值品统计
 *     summary: 获取高价值品效率分析数据
 *     description: 分析高价值品的配置效率、投入产出比和整体效率指标，支持时间筛选
 *     parameters:
 *       - in: query
 *         name: startTime
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始时间 (YYYY-MM-DD)
 *         example: "2023-01-01"
 *       - in: query
 *         name: endTime
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束时间 (YYYY-MM-DD)
 *         example: "2023-12-31"
 *     responses:
 *       200:
 *         description: 成功获取效率分析数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
 *                   type: integer
 *                   example: 200
 *                   description: 响应状态码
 *                 message:
 *                   type: string
 *                   example: "效率分析获取成功"
 *                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     configurationEfficiency:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 标题
 *                         units:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               unitName:
 *                                 type: string
 *                                 description: 单位名称
 *                               totalQuantity:
 *                                 type: number
 *                                 description: 总数量
 *                               totalAmount:
 *                                 type: string
 *                                 description: 总金额
 *                               recordCount:
 *                                 type: number
 *                                 description: 记录数
 *                               avgValuePerItem:
 *                                 type: string
 *                                 description: 单品平均价值
 *                               efficiencyScore:
                                 type: string
                                 description: 效率评分
 *                     riskManagement:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 标题
 *                         riskCategories:
 *                           type: object
 *                           properties:
 *                             high_value_risk:
 *                               type: number
 *                               description: 高价值风险数量
 *                             high_quantity_risk:
 *                               type: number
 *                               description: 高数量风险数量
 *                             high_unit_value_risk:
 *                               type: number
 *                               description: 高单位价值风险数量
 *                             high_frequency_risk:
 *                               type: number
 *                               description: 高频率风险数量
 *                             normal:
 *                               type: number
 *                               description: 正常数量
 *                         riskUnits:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               unitName:
 *                                 type: string
 *                                 description: 单位名称
 *                               totalQuantity:
 *                                 type: number
 *                                 description: 总数量
 *                               totalAmount:
 *                                 type: string
 *                                 description: 总金额
 *                               recordCount:
 *                                 type: number
 *                                 description: 记录数
 *                               avgValuePerItem:
 *                                 type: string
 *                                 description: 单品平均价值
 *                               # 风险等级字段已移除，仅保留基础数据
 *                     balanceAssessment:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 标题
 *                         statistics:
 *                           type: object
 *                           properties:
 *                             totalUnits:
 *                               type: number
 *                               description: 总单位数
 *                             distribution:
 *                               type: object
 *                               properties:
 *                                 avgQuantityPerUnit:
 *                                   type: string
 *                                   description: 单位平均数量
 *                                 quantityStdDev:
 *                                   type: string
 *                                   description: 数量标准差
 *                                 quantityCoeffVar:
 *                                   type: string
 *                                   description: 数量变异系数
 *                                 quantityRange:
 *                                   type: object
 *                                   properties:
 *                                     max:
 *                                       type: number
 *                                       description: 最大数量
 *                                     min:
 *                                       type: number
 *                                       description: 最小数量
 *                                     span:
 *                                       type: number
 *                                       description: 数量跨度
 *                             amountDistribution:
 *                               type: object
 *                               properties:
 *                                 avgAmountPerUnit:
 *                                   type: string
 *                                   description: 单位平均金额
 *                                 amountStdDev:
 *                                   type: string
 *                                   description: 金额标准差
 *                                 amountCoeffVar:
 *                                   type: string
 *                                   description: 金额变异系数
 *                                 amountRange:
 *                                   type: object
 *                                   properties:
 *                                     max:
 *                                       type: string
 *                                       description: 最大金额
 *                                     min:
 *                                       type: string
 *                                       description: 最小金额
 *                                     span:
 *                                       type: string
 *                                       description: 金额跨度
 *                             # 均衡性评分已移除，仅保留统计数据
 *                     # 配置建议部分已移除，仅保留基础统计数据
 *                     roiAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 标题
 *                         units:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               rank:
 *                                 type: number
 *                                 description: 排名
 *                               unitName:
 *                                 type: string
 *                                 description: 单位名称
 *                               totalQuantity:
 *                                 type: number
 *                                 description: 总数量
 *                               totalAmount:
 *                                 type: string
 *                                 description: 总金额
 *                               recordCount:
 *                                 type: number
 *                                 description: 记录数
 *                               quantityEfficiency:
 *                                 type: string
 *                                 description: 数量效率
 *                               amountEfficiency:
 *                                 type: string
 *                                 description: 金额效率
 *                               quantityPerMillionYuan:
 *                                 type: string
 *                                 description: 每百万元数量
 *                               # 效率类别字段已移除，仅保留计算数据
 *                     overallEfficiency:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 标题
 *                         metrics:
 *                           type: object
 *                           properties:
 *                             totalUnits:
 *                               type: number
 *                               description: 总单位数
 *                             grandTotalQuantity:
 *                               type: number
 *                               description: 总数量
 *                             grandTotalAmount:
 *                               type: string
 *                               description: 总金额
 *                             grandTotalRecords:
 *                               type: number
 *                               description: 总记录数
 *                             efficiency:
 *                               type: object
 *                               properties:
 *                                 avgQuantityPerUnit:
 *                                   type: string
 *                                   description: 单位平均数量
 *                                 avgAmountPerUnit:
 *                                   type: string
 *                                   description: 单位平均金额
 *                                 avgQuantityPerRecord:
 *                                   type: string
 *                                   description: 记录平均数量
 *                                 avgAmountPerRecord:
 *                                   type: string
 *                                   description: 记录平均金额
 *                                 overallValuePerItem:
 *                                   type: string
 *                                   description: 整体单品价值
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
const { success, error: createError, handleResponse } = require('../../../lib/response');
const { query } = require('../../../lib/db');

/**
 * 处理高价值品效率分析请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    // 获取时间筛选参数
    const { startTime, endTime } = req.query;
    
    // 构建时间筛选条件
    let timeCondition = '';
    if (startTime || endTime) {
      const conditions = [];
      if (startTime) conditions.push(`TJSJ >= '${startTime}'`);
      if (endTime) conditions.push(`TJSJ <= '${endTime}'`);
      timeCondition = ` WHERE ${conditions.join(' AND ')}`;
    }
    
    // 配置效率分析
    const configurationEfficiencyQuery = `
      SELECT 
        COALESCE(DWMC, '未知单位') as unitName,
        SUM(COALESCE(GJZSL, 0)) as totalQuantity,
        SUM(COALESCE(GJZJE, 0)) as totalAmount,
        COUNT(*) as recordCount,
        SUM(COALESCE(GJZJE, 0)) / NULLIF(SUM(COALESCE(GJZSL, 0)), 0) as avgValuePerItem,
        SUM(COALESCE(GJZSL, 0)) / COUNT(*) as quantityPerRecord,
        SUM(COALESCE(GJZJE, 0)) / COUNT(*) as amountPerRecord,
        CASE 
          WHEN SUM(COALESCE(GJZSL, 0)) = 0 THEN 0
          ELSE SUM(COALESCE(GJZJE, 0)) / SUM(COALESCE(GJZSL, 0))
        END as efficiencyScore
      FROM wd_zcgk_gjzzcdwfb${timeCondition}
      GROUP BY DWMC
      HAVING totalQuantity > 0 AND totalAmount > 0
      ORDER BY efficiencyScore DESC
      LIMIT 20
    `;

    const efficiencyResultResult = await query(configurationEfficiencyQuery);

    // 风险识别分析
    const riskAnalysisQuery = `
      SELECT 
        COALESCE(DWMC, '未知单位') as unitName,
        SUM(COALESCE(GJZSL, 0)) as totalQuantity,
        SUM(COALESCE(GJZJE, 0)) as totalAmount,
        COUNT(*) as recordCount,
        SUM(COALESCE(GJZJE, 0)) / NULLIF(SUM(COALESCE(GJZSL, 0)), 0) as avgValuePerItem,
        -- 风险等级计算已移除，仅保留基础数据
        'data_only' as dataType
      FROM wd_zcgk_gjzzcdwfb${timeCondition}
      GROUP BY DWMC
      HAVING totalQuantity > 0 AND totalAmount > 0
      ORDER BY totalAmount DESC
    `;

    const riskResultResult = await query(riskAnalysisQuery);

    // 均衡性评估
    const balanceAssessmentQuery = `
      SELECT 
        COUNT(*) as totalUnits,
        AVG(unit_quantity) as avgQuantityPerUnit,
        STDDEV(unit_quantity) as quantityStdDev,
        AVG(unit_amount) as avgAmountPerUnit,
        STDDEV(unit_amount) as amountStdDev,
        (STDDEV(unit_quantity) / NULLIF(AVG(unit_quantity), 0)) * 100 as quantityCoeffVar,
        (STDDEV(unit_amount) / NULLIF(AVG(unit_amount), 0)) * 100 as amountCoeffVar,
        MAX(unit_quantity) as maxQuantity,
        MIN(unit_quantity) as minQuantity,
        MAX(unit_amount) as maxAmount,
        MIN(unit_amount) as minAmount
      FROM (
        SELECT 
          DWMC,
          SUM(COALESCE(GJZSL, 0)) as unit_quantity,
          SUM(COALESCE(GJZJE, 0)) as unit_amount
        FROM wd_zcgk_gjzzcdwfb
        WHERE DWMC IS NOT NULL AND DWMC != ''${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
        GROUP BY DWMC
        HAVING unit_quantity > 0 OR unit_amount > 0
      ) unit_stats
    `;

    const balanceResultResult = await query(balanceAssessmentQuery);
    const balanceData = balanceResultResult[0];

    // 配置建议分析已移除，仅保留基础统计

    // 投入产出比分析
    const roiAnalysisQuery = `
      SELECT 
        unit_stats.unitName,
        unit_stats.totalQuantity,
        unit_stats.totalAmount,
        unit_stats.recordCount,
        unit_stats.quantityEfficiency,
        unit_stats.amountEfficiency,
        unit_stats.quantityPerMillionYuan,
        -- 效率类别评估已移除，仅保留计算数据
        'data_only' as dataType
      FROM (
        SELECT 
          COALESCE(DWMC, '未知单位') as unitName,
          SUM(COALESCE(GJZSL, 0)) as totalQuantity,
          SUM(COALESCE(GJZJE, 0)) as totalAmount,
          COUNT(*) as recordCount,
          SUM(COALESCE(GJZSL, 0)) / COUNT(*) as quantityEfficiency,
          SUM(COALESCE(GJZJE, 0)) / COUNT(*) as amountEfficiency,
          SUM(COALESCE(GJZSL, 0)) / NULLIF(SUM(COALESCE(GJZJE, 0)), 0) * 1000000 as quantityPerMillionYuan
        FROM wd_zcgk_gjzzcdwfb
        WHERE DWMC IS NOT NULL AND DWMC != ''${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
        GROUP BY DWMC
        HAVING totalQuantity > 0 AND totalAmount > 0 AND recordCount > 0
      ) unit_stats
      CROSS JOIN (
        SELECT 
          AVG(quantityEfficiency) as avgQuantityEfficiency
        FROM (
          SELECT 
            SUM(COALESCE(GJZSL, 0)) / COUNT(*) as quantityEfficiency
          FROM wd_zcgk_gjzzcdwfb
          WHERE DWMC IS NOT NULL AND DWMC != ''${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
          GROUP BY DWMC
        ) efficiency_stats
      ) avg_stats
      ORDER BY unit_stats.quantityEfficiency DESC
      LIMIT 20
    `;

    const roiResultResult = await query(roiAnalysisQuery);

    // 整体效率指标
    const overallEfficiencyQuery = `
      SELECT 
        COUNT(DISTINCT DWMC) as totalUnits,
        SUM(COALESCE(GJZSL, 0)) as grandTotalQuantity,
        SUM(COALESCE(GJZJE, 0)) as grandTotalAmount,
        COUNT(*) as grandTotalRecords,
        SUM(COALESCE(GJZSL, 0)) / COUNT(DISTINCT DWMC) as avgQuantityPerUnit,
        SUM(COALESCE(GJZJE, 0)) / COUNT(DISTINCT DWMC) as avgAmountPerUnit,
        SUM(COALESCE(GJZSL, 0)) / COUNT(*) as avgQuantityPerRecord,
        SUM(COALESCE(GJZJE, 0)) / COUNT(*) as avgAmountPerRecord,
        SUM(COALESCE(GJZJE, 0)) / NULLIF(SUM(COALESCE(GJZSL, 0)), 0) as overallValuePerItem
      FROM wd_zcgk_gjzzcdwfb
      WHERE DWMC IS NOT NULL AND DWMC != ''${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
    `;

    const overallResultResult = await query(overallEfficiencyQuery);
    const overallData = overallResultResult[0];

    const responseData = {
      configurationEfficiency: {
        title: "配置效率分析(Top20)",
        units: efficiencyResultResult.map((row, index) => ({
          rank: index + 1,
          unitName: row.unitName,
          totalQuantity: parseInt(row.totalQuantity || 0),
          totalAmount: parseFloat(row.totalAmount || 0).toFixed(2),
          recordCount: parseInt(row.recordCount),
          avgValuePerItem: parseFloat(row.avgValuePerItem || 0).toFixed(2),
          quantityPerRecord: parseFloat(row.quantityPerRecord || 0).toFixed(2),
          amountPerRecord: parseFloat(row.amountPerRecord || 0).toFixed(2),
          efficiencyScore: parseFloat(row.efficiencyScore || 0).toFixed(2)
        }))
      },
      riskManagement: {
        title: "风险管理分析",
        // 风险分类统计已移除，仅保留基础数据
        totalUnits: riskResultResult.length,
        riskUnits: riskResultResult.map(row => ({
          unitName: row.unitName,
          totalQuantity: parseInt(row.totalQuantity || 0),
          totalAmount: parseFloat(row.totalAmount || 0).toFixed(2),
          recordCount: parseInt(row.recordCount),
          avgValuePerItem: parseFloat(row.avgValuePerItem || 0).toFixed(2)
          // 风险等级字段已移除
        }))
      },
      balanceAssessment: {
        statistics: {
          totalUnits: parseInt(balanceData.totalUnits || 0),
          distribution: {
            avgQuantityPerUnit: parseFloat(balanceData.avgQuantityPerUnit || 0).toFixed(2),
            quantityStdDev: parseFloat(balanceData.quantityStdDev || 0).toFixed(2),
            quantityCoeffVar: parseFloat(balanceData.quantityCoeffVar || 0).toFixed(2),
            quantityRange: {
              max: parseInt(balanceData.maxQuantity || 0),
              min: parseInt(balanceData.minQuantity || 0),
              span: (parseInt(balanceData.maxQuantity || 0) - parseInt(balanceData.minQuantity || 0))
            }
          },
          amountDistribution: {
            avgAmountPerUnit: parseFloat(balanceData.avgAmountPerUnit || 0).toFixed(2),
            amountStdDev: parseFloat(balanceData.amountStdDev || 0).toFixed(2),
            amountCoeffVar: parseFloat(balanceData.amountCoeffVar || 0).toFixed(2),
            amountRange: {
              max: parseFloat(balanceData.maxAmount || 0).toFixed(2),
              min: parseFloat(balanceData.minAmount || 0).toFixed(2),
              span: (parseFloat(balanceData.maxAmount || 0) - parseFloat(balanceData.minAmount || 0)).toFixed(2)
            }
          },
          // 均衡性评分已移除，仅保留统计数据
        }
      },
      // 配置建议部分已移除，仅保留基础统计数据
      roiAnalysis: {

        units: roiResultResult.map((row, index) => ({
          rank: index + 1,
          unitName: row.unitName,
          totalQuantity: parseInt(row.totalQuantity || 0),
          totalAmount: parseFloat(row.totalAmount || 0).toFixed(2),
          recordCount: parseInt(row.recordCount),
          quantityEfficiency: parseFloat(row.quantityEfficiency || 0).toFixed(2),
          amountEfficiency: parseFloat(row.amountEfficiency || 0).toFixed(2),
          quantityPerMillionYuan: parseFloat(row.quantityPerMillionYuan || 0).toFixed(2)
          // 效率类别字段已移除
        }))
      },
      overallEfficiency: {

        metrics: {
          totalUnits: parseInt(overallData.totalUnits || 0),
          grandTotalQuantity: parseInt(overallData.grandTotalQuantity || 0),
          grandTotalAmount: parseFloat(overallData.grandTotalAmount || 0).toFixed(2),
          grandTotalRecords: parseInt(overallData.grandTotalRecords || 0),
          efficiency: {
            avgQuantityPerUnit: parseFloat(overallData.avgQuantityPerUnit || 0).toFixed(2),
            avgAmountPerUnit: parseFloat(overallData.avgAmountPerUnit || 0).toFixed(2),
            avgQuantityPerRecord: parseFloat(overallData.avgQuantityPerRecord || 0).toFixed(2),
            avgAmountPerRecord: parseFloat(overallData.avgAmountPerRecord || 0).toFixed(2),
            overallValuePerItem: parseFloat(overallData.overallValuePerItem || 0).toFixed(2)
          }
        }
      }
    };

    return handleResponse(res, success(responseData, "效率分析获取成功"));
    
  } catch (err) {
    console.error(arguments[1], err);
    return handleResponse(res, createError("获取效率分析失败", 500));
  }
}



export default withCors(handler, ['GET']);